{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell",
            "language": "powershell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "## **Azure SQL Migration**\n",
                "\n",
                "![]()"
            ],
            "metadata": {
                "azdata_cell_guid": "ac64bb64-cf65-48c3-a392-4a0a809ea452"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Azure SQL Migration Steps\n",
                "\n",
                "1. Check Az PowerShell module\n",
                "    \n",
                "2. Input parameters\n",
                "    \n",
                "3. Connect to Azure account\n",
                "    \n",
                "4. Validate input parameters\n",
                "    \n",
                "5. Create Migration Controller\n",
                "    \n",
                "6. Download, Install and Register Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)\n",
                "    \n",
                "7. Start MI online migration\n",
                "    \n",
                "8. Monitor ongoing migration\n",
                "    \n",
                "9. Initiate migration cutover\n",
                "    \n",
                "10. Check status of migrated database\n",
                "    \n",
                "\n",
                "      Appendix  \n",
                "           A) Cancel migration\n",
                "\n",
                "           B) Delete migration controllers"
            ],
            "metadata": {
                "azdata_cell_guid": "e479b550-d6bd-49c5-965a-34a7d1d16412"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 1\\. Check Az PowerShell module\n",
                "\n",
                "Run below code cell to check for powershell version and  if Azure Az powershell module is installed. Reccomand to run this cell with Administrator rights (open ADS as Administrator) . \n",
                "\n",
                "This notebook needs Az PowerShell module ([Introducing the Azure Az PowerShell module | Microsoft Docs](https://docs.microsoft.com/powershell/azure/new-azureps-module-az?view=azps-5.3.0))"
            ],
            "metadata": {
                "azdata_cell_guid": "433e6583-5a5c-49e2-8ebb-9fdbd1dd9a00"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Check if Az PowerShell module is installed\r\n",
                "if ($PSVersionTable.PSEdition -eq 'Desktop' -and (Get-Module -Name AzureRM -ListAvailable)) {\r\n",
                "    Write-Warning -Message ('Az module not installed. Having both the AzureRM and ' +\r\n",
                "      'Az modules installed at the same time is not supported.')\r\n",
                "    Write-Warning -Message ('If you want to uninstall AzureRM module, open Azure Data Studio using Run as administrator and run this code cell again ')\r\n",
                "    Uninstall-Module AzureRM -AllVersions\r\n",
                "} \r\n",
                "\r\n",
                "if( !(Get-Module -Name AnyBox) ){\r\n",
                "   Write-Output \"installing AnyBox module\"\r\n",
                "   Install-Module -Name AnyBox -RequiredVersion 0.3.4 -Force\r\n",
                "   Import-Module AnyBox\r\n",
                "}\r\n",
                "\r\n",
                "$version = [System.Version]::Parse(\"5.3.0\")\r\n",
                "\r\n",
                "$AzModule = Get-InstalledModule -Name Az -ErrorAction:Ignore\r\n",
                "if( !$AzModule ){\r\n",
                "  Write-Output \"No Azure Powershell modue was found\"\r\n",
                "  Write-Output \"This requires Admin rights: force to install or update Azure PowerShell Modules with the latest version...\"\r\n",
                "  Install-Module -Name Az -AllowClobber -Force \r\n",
                "}else{\r\n",
                "   $AzVersion = $AzModule.Version\r\n",
                "   if( $AzVersion -and $AzVersion -ge $version){\r\n",
                "     Write-Output \"You have Azure Module installed already, version is $AzVersion \"\r\n",
                "   }else{\r\n",
                "     Write-Output \"Your Azure Module version is $AzVersion, lower than the required version  $version\"\r\n",
                "     Write-Output \"This requires Admin rights: force to install or update Azure PowerShell Modules with the latest version...\"\r\n",
                "     Install-Module -Name Az -AllowClobber -Force\r\n",
                "   }\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "21b6bf77-cc53-44c0-9d9b-e59987215150",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 2\\. Input parameters\n",
                "\n",
                "Please provide values for all the parameters in below code cell and run it. The values will be used for performing migration to Azure SQL Managed Instance."
            ],
            "metadata": {
                "azdata_cell_guid": "98f53876-a941-48cb-811d-6f7a899d4426"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "<#=====================================================================================================================================================\r\n",
                "Azure Subscription and Tenant ID\r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input Azure Subscription ID \r\n",
                "$subscription            = \"\"\r\n",
                "# Input Azure Active Directory Tenant ID (Find Tetant ID through Azure Portal https://docs.microsoft.com/azure/active-directory/fundamentals/active-directory-how-to-find-tenant#find-tenant-id-through-the-azure-portal)\r\n",
                "$tenantID                = \"\" \r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Source SQL Server database \r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input source SQL Server instance name\r\n",
                "$SourceSQLServer         = \"\" \r\n",
                "# Input source SQL Server database name\r\n",
                "$SourceDbName            = \"\"\r\n",
                "# Choose SQL Server authentication mode. Valid values:  \"WindowsAuthentication\" , \"SqlAuthentication\" \r\n",
                "$Authentication          = \"SqlAuthentication\"\r\n",
                "# Input source SQL Server user name. Fill in remaining parameters and run this code cell to enter password. \r\n",
                "$SourceSQLServerUserName = \"\"\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "File share \r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input file share path where database backup files exits\r\n",
                "$FileSharePath           = \"\"\r\n",
                "# Input user name that has read access to file share. Fill in remaining parameters and run this code cell to enter password.  \r\n",
                "$FileShareUserName       = \"\"\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Target Azure SQL Managed Instance (MI) \r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input Resource Group name that contains target MI \r\n",
                "$MIRgName           = \"\"                                   \r\n",
                "# Input target MI name\r\n",
                "$MIName             = \"\"\r\n",
                "# Input target MI database name . Azure Database Migration service (DMS) will create the target database. \r\n",
                "$TargetDbName       = \"\"                           \r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Azure Storage Account\r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input Resorce Group name that contains the Storage Account where backup files will be copied to. \r\n",
                "$StorageRgName      = \"\"\r\n",
                "# Input Storage Account name where bakcup files will be copied to.\r\n",
                "$StorageAccountName = \"\"                           \r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Migration Mode\r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Choose migration mode. Valid values: \"online\" , \"offline\"\r\n",
                "$MigrationMode           = \"online\"                                                        \r\n",
                "\r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Migration Controller\r\n",
                "=====================================================================================================================================================#>\r\n",
                "\r\n",
                "# Input Resoruce Group to be used for creating Migration Controller. If Resource Group doesn't exist then it will be created. \r\n",
                "$ResourceGroupName  = \"\"\r\n",
                "# Input the name of  Migration Controller to be created. \r\n",
                "$ControllerName     = \"\"                   \r\n",
                "# Input Azure location for the Migration Controller resource. Valid values: \"eastus2euap\"\r\n",
                "$Location           = \"\"                      \r\n",
                "\r\n",
                "<#=====================================================================================================================================================\r\n",
                "Following code will let you enter password for SQL Server user and file share user. \r\n",
                "=====================================================================================================================================================#>\r\n",
                "Import-Module AnyBox\r\n",
                "$anybox = New-Object AnyBox.AnyBox\r\n",
                "$anybox.Prompts = @(\r\n",
                "  New-AnyBoxPrompt -Name 'SourceServerPassword' -Message \"Enter password for SQL Server user \"\"$SourceSQLServerUserName\"\" \"  -ValidateNotEmpty -InputType Password\r\n",
                "  New-AnyBoxPrompt -Name 'FilesharePassword' -Message \"Enter password for file share user \"\"$FileShareUserName\"\" \" -ValidateNotEmpty -InputType Password\r\n",
                ")\r\n",
                "$anybox.Buttons = @(\r\n",
                "    New-AnyBoxButton -Name 'submit' -Text 'Submit' -IsDefault\r\n",
                ")\r\n",
                "$anybox.ContentAlignment = 'Left'\r\n",
                "$response = $anybox | Show-AnyBox\r\n",
                "$SourceSQLServerPasswordS = $response['SourceServerPassword']\r\n",
                "$FileSharePasswordS = $response['FilesharePassword']\r\n",
                "$SourceBSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SourceSQLServerPasswordS)\r\n",
                "$FileShareBSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($FileSharePasswordS)\r\n",
                "$SourceSQLServerPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($SourceBSTR)\r\n",
                "$FileSharePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($FileShareBSTR)\r\n",
                "$HostName = \"https://\" + $Location + \".management.azure.com\"\r\n",
                "$APIversion = \"api-version=2020-09-01-preview\""
            ],
            "metadata": {
                "azdata_cell_guid": "3759257c-05ec-43aa-b1e7-651252fff219",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 3\\. Connect to Azure Account\n",
                "\n",
                "Run below code cell to:\n",
                "\n",
                "-   Connect to an Azure connect\n",
                "    \n",
                "-   Check if resource group and storage account where backup files will be copied to exists. Get the storage account key.\n",
                "    \n",
                "-   Check if resource group for Azure Data Migration Service (DMS) Migration Controller exists. \n",
                "    \n",
                "-   Register Azure Data Migration Service (DMS) Migration Controller resource type"
            ],
            "metadata": {
                "azdata_cell_guid": "b06fee5e-355d-47fc-8c1f-41294756cc87"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Connect to Azure Subscription\r\n",
                "Connect-AzAccount -Subscription $subscription -Tenant $tenantID\r\n",
                "\r\n",
                "$StorageAccount = Get-AzStorageAccount -Name $StorageAccountName -ResourceGroupName $StorageRgName -ErrorAction Ignore\r\n",
                "if (!$StorageAccount)\r\n",
                "{\r\n",
                "    Write-Output \"Storage Account $StorageAccountName in resource group $StorageRgName does not exist.\"\r\n",
                "    Write-Output \"Please check the storage account information and run this cell again\"\r\n",
                "}\r\n",
                "else\r\n",
                "{\r\n",
                "    Write-Output \"Storage Account $StorageAccountName already exists\"\r\n",
                "    # Get the storage account key\r\n",
                "    $StorageAccountKeyObj = Get-AzStorageAccountKey -ResourceGroupName $StorageRgName -Name $StorageAccountName\r\n",
                "    $StorageAccountKey = $StorageAccountKeyObj.Value[0]\r\n",
                "    $StorageAccountResourceId = \"/subscriptions/\" + $subscription + \"/resourceGroups/\" + $StorageRgName + \"/providers/Microsoft.Storage/storageAccounts/\" + $StorageAccountName\r\n",
                "}\r\n",
                "\r\n",
                "# Check if Resoruce Group for Migration Controller exists. If it doesn't exist then create it\r\n",
                "$rg = Get-AzResourceGroup | Where ResourceGroupName -eq $ResourceGroupName\r\n",
                "if (!$rg)\r\n",
                "{\r\n",
                "    # Need to create a new resource group\r\n",
                "    Write-Output \"Resource Group $ResourceGroupName does not exist. Creating...\"\r\n",
                "    $rg = New-AzResourceGroup -Name $ResourceGroupName -Location $Location\r\n",
                "    if( !$rg ){\r\n",
                "        Write-Output \"Creating Resource Group $ResourceGroupName failed. Check your permission in the subscription $subscription\"\r\n",
                "    }else{\r\n",
                "        Write-Output \"Resource Group $ResourceGroupName has been created\"\r\n",
                "    }\r\n",
                "}else{\r\n",
                "    Write-Output \"Resource Group $ResourceGroupName exists.\"\r\n",
                "}\r\n",
                "\r\n",
                "# Check if Azure Database Migration Service (DMS) Migration Controller resource is registered in the subscription. \r\n",
                "if (Get-AzResource -ResourceType Microsoft.DataMigration/Controllers) {\r\n",
                "    Write-Output \"Azure Database Migration Service (DMS) Migration Controller resource type is already registered in the subscription\"\r\n",
                "}else{\r\n",
                "    Write-Output \"Registering Azure Database Migration Service (DMS) Migration Controller resource type in the subscription\"\r\n",
                "    if(Get-AzResourceProvider -ProviderNamespace Microsoft.DataMigration){\r\n",
                "        Unregister-AzResourceProvider -ProviderNamespace Microsoft.DataMigration\r\n",
                "    }\r\n",
                "    Register-AzResourceProvider -ProviderNamespace Microsoft.DataMigration\r\n",
                "    if( (Get-AzResourceProvider -ProviderNamespace Microsoft.DataMigration).ResourceTypes | Where-Object ResourceTypeName -eq Controllers ){\r\n",
                "        Write-Output \"Azure Database Migration Service (DMS) Migration Controller resource type has been registered in the subscription\"\r\n",
                "    }else{\r\n",
                "        Write-Output \"Azure Database Migration Service (DMS) Migration Controller resource type registration failed.\"\r\n",
                "    }\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "4c95aa7f-b3e9-46be-910a-e67f83fe3921",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 4\\. Validate input parameters\n",
                "\n",
                "Run this code cell to perform following validations:\n",
                "\n",
                "- Ensure this machine can connect to source SQL Server. Assuming the Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR) is installed in this local machine. \n",
                "- Ensure this machine has access to fileshare. Assuming the ADF SHIR is installed in this local machine. \n",
                "- Ensure the target Azure SQL Managed Instance exists"
            ],
            "metadata": {
                "azdata_cell_guid": "48a763bd-0569-4640-a167-ff4adb4f6ae2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Test Connection to  source SQL Server.\r\n",
                "Write-Output \"Checking  connection to source SQL Server instance $SourceSQLServer ...\"\r\n",
                "if($Authentication -eq \"WindowsAuthentication\"){\r\n",
                "    $ParseNameInstance = $SourceSQLServer -split '\\',-1,'SimpleMatch'\r\n",
                "    $SourceServerName = $ParseNameInstance[0]\r\n",
                "    [securestring]$secSourcePasswordSql = ConvertTo-SecureString $SourceSQLServerPassword -AsPlainText -Force\r\n",
                "    [pscredential]$credObjectSql = New-Object System.Management.Automation.PSCredential ($SourceSQLServerUserName, $secSourcePasswordSql)\r\n",
                "    $TestDriveSql = New-PSDrive TestSql -PSProvider FileSystem -Root \"\\\\$SourceServerName\\c$\" -Credential $credObjectSql\r\n",
                "    if(!$TestDriveSql){\r\n",
                "        Write-Output \"Connection from this machine to source SQL Server instance failed\" \r\n",
                "    }else{\r\n",
                "        Write-Output \"Connection from this machine to source SQL Server instance succeeded\"\r\n",
                "    }\r\n",
                "    Remove-PSDrive TestSql\r\n",
                "    Remove-SmbMapping -Force -ErrorAction Ignore\r\n",
                "}\r\n",
                "else{\r\n",
                "    $AuthTyp = \"\"\r\n",
                "    switch ($Authentication){\r\n",
                "        \"SqlAuthentication\" { $AuthTyp = \"\"; break}\r\n",
                "        \"ActiveDirectoryIntegrated\" { $AuthTyp = \"Active Directory Integrated\"; break}\r\n",
                "        \"ActiveDirectoryPassword\" { $AuthTyp = \"Active Directory Password\"; break}\r\n",
                "        default { Write-Output \"Unvalid input of authentication type\" }\r\n",
                "    }\r\n",
                "    #$Authentication -eq \"SqlAuthentication\" ;Authentication={4} ,$AuthTyp\r\n",
                "    $AuthTyp\r\n",
                "    $connectionString = 'Data Source={0};database={1};User ID={2};Password={3};Authentication={4}' -f $SourceSQLServer,$SourceDbName,$SourceSQLServerUserName,$SourceSQLServerPassword,$AuthTyp\r\n",
                "    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString\r\n",
                "    try{\r\n",
                "        $sqlConnection.Open()\r\n",
                "        $sqlConnection.Close()\r\n",
                "        Write-Output \"Connection from this machine to source SQL Server instance succeeded\" \r\n",
                "    } catch {\r\n",
                "        Write-Output \"Connection from this machine to source SQL Server instance failed\" \r\n",
                "    }\r\n",
                "}\r\n",
                "\r\n",
                "# Test Connection to file share path \r\n",
                "Write-Output \"Checking connection to file share path $FileSharePath ...\"\r\n",
                "[securestring]$secStringPassword = ConvertTo-SecureString $FileSharePassword -AsPlainText -Force\r\n",
                "[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($FileShareUserName, $secStringPassword)\r\n",
                "$TestDrive = New-PSDrive Test -PSProvider FileSystem -Root $FileSharePath -Credential $credObject\r\n",
                "if(!$TestDrive){\r\n",
                "    Write-Output \"Connection from this machine to file share path failed\"\r\n",
                "}else{\r\n",
                "    Write-Output \"Connection from this machine to file share path succeeded\"\r\n",
                "}\r\n",
                "$LastBackupName = \"\"\r\n",
                "$filenames = (Get-ChildItem -Path $FileSharePath).Name\r\n",
                "foreach ($item in $filenames){\r\n",
                "   $extn = [IO.Path]::GetExtension($item)\r\n",
                "   if ($extn -eq \".bak\" ){\r\n",
                "       $LastBackupName = $item\r\n",
                "       break;\r\n",
                "   }\r\n",
                "}\r\n",
                "if ( !$LastBackupName -and $MigrationMode -eq \"offline\"){\r\n",
                "    Write-Warning -Message ('No database backup file was found in file share path')\r\n",
                "}\r\n",
                "Remove-PSDrive Test\r\n",
                "Remove-SmbMapping -Force -ErrorAction Ignore\r\n",
                "\r\n",
                "# Validate target MI exists\r\n",
                "Write-Output \"Checking the Managed Instance target server $MIName in resource group $MIRgName ...\" \r\n",
                "$MIInstanceObj = Get-AzSqlInstance -ResourceGroupName $MIRgName -Name $MIName\r\n",
                "if(!$MIInstanceObj){\r\n",
                "    Write-Output \"Target Managed Instance does not exist.\" \r\n",
                "}else{\r\n",
                "    Write-Output \"Target Managed Instance exists.\"\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "442320f4-b9f9-4d77-89fa-4646d1e9af10",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 5\\. Create DMS Migration Controller\n",
                "\n",
                "Migration Controller in an Azure ARM (Azure Resource Manager) resource created in the subscription and it is needed to coordinate and monitor data migration activities."
            ],
            "metadata": {
                "azdata_cell_guid": "b25dca9e-269b-45db-8cdf-efa53e2213d2"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 5.1 Run below code cell to create a Migration Controller"
            ],
            "metadata": {
                "azdata_cell_guid": "708643af-4358-4c4a-9475-3d7653ea578c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Create Migration Controller\r\n",
                "$ControllersRescourceId = \"/subscriptions/\" + $subscription + \"/resourceGroups/\" + $ResourceGroupName + \"/providers/Microsoft.DataMigration/Controllers/\" + $ControllerName\r\n",
                "$CtlRequestBody = '\r\n",
                "{\r\n",
                "\"location\": null\r\n",
                "}' | ConvertFrom-Json\r\n",
                "$CtlRequestBody.location=$Location \r\n",
                "$CtlRequestBodyJson = $CtlRequestBody | ConvertTo-Json\r\n",
                "$ControllersURL = $HostName +  $ControllersRescourceId + \"?\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCtlPUT = Invoke-WebRequest -Uri $ControllersURL -Header $headers -Method PUT -Body $CtlRequestBodyJson  -ContentType \"application/json\"  -UseBasicParsing\r\n",
                "Write-Output \"Response for Migration Controller creation: \"\r\n",
                "$ResponseOfCtlPUT\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "40d0c992-860a-4b83-8104-16ec7e6e7983",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 5.2 Run below code cell to get creation status for Migration Controller"
            ],
            "metadata": {
                "azdata_cell_guid": "f449d75e-c510-41ab-9ea1-8c2652e8fe98"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Check Migration Controller creation status\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCtlGET = Invoke-RestMethod -Uri $ControllersURL -Header $headers -Method GET -ContentType \"application/json\" -UseBasicParsing\r\n",
                "$ResponseOfCtlGET | ConvertTo-Json\r\n",
                "if ( $ResponseOfCtlGET.properties.isProvisioned ){\r\n",
                "   if($ResponseOfCtlGET.properties.integrationRuntimeState -eq \"NeedRegistration\"){\r\n",
                "      Write-Output \"Migration Controller has been provisioned. Run code in step 5.3 to get authentication keys and register Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)\"\r\n",
                "   }else{\r\n",
                "      Write-Output \"Migration Controller has been provisioned and registered with Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR). Run code in step 7 to start migration\"\r\n",
                "   }\r\n",
                "} else {\r\n",
                "     Write-Output \"Migration Controller is still being provisioned. Check the status again in a moment...\"\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "4ec666cb-1545-497c-8083-23651138161d",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 5.3 Run below code cell to list Migration Controller's authentication keys, needed for registering Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)"
            ],
            "metadata": {
                "azdata_cell_guid": "7268b3cd-8a23-4565-9ffc-6033bee365f6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Get Migration Controller's authentication keys, needed for registering Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)\r\n",
                "$ListAuthkeysURL = $HostName +  $ControllersRescourceId + \"/ListAuthKeys\" + \"?\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfListAuthkeys = Invoke-RestMethod -Uri $ListAuthkeysURL -Header $headers -Method POST -ContentType \"application/json\" -UseBasicParsing\r\n",
                "Write-Output \"Use one of the two authetication keys to register Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)\"\r\n",
                "$ResponseOfListAuthkeys.keyName1\r\n",
                "$ResponseOfListAuthkeys.keyName2"
            ],
            "metadata": {
                "azdata_cell_guid": "cac14ac0-421d-4024-ad88-be433801f3c6",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 6\\. Download, Install and Register Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR)"
            ],
            "metadata": {
                "azdata_cell_guid": "bb7fd22c-951c-42d6-9c5b-ad99a4f67c9e"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 6.1 Download, install and register ADF SHIR\n",
                "\n",
                "Download and install ADF SHIR on this machine ([Download Microsoft Integration Runtime from Official Microsoft Download Center](https://www.microsoft.com/download/details.aspx?id=39717))\n",
                "\n",
                "On the Register Integration Runtime (Self-hosted) page, paste the key you saved earlier (Step 5.3), and select Register.\n",
                "\n",
                "![Register the integration runtime](https://docs.microsoft.com/azure/data-factory/media/create-self-hosted-integration-runtime/register-integration-runtime.png)\n",
                "\n",
                "<span style=\"background-color: rgb(255, 255, 255); color: rgb(23, 23, 23); font-family: &quot;Segoe UI&quot;, SegoeUI, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif; font-size: 16px;\">After the self-hosted integration runtime is registered successfully, you see the following window:</span>  \n",
                "\n",
                "![Successful registration](https://docs.microsoft.com/azure/data-factory/media/create-self-hosted-integration-runtime/registered-successfully.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "64e475bc-b94e-49a2-ac2a-9c0b5435efac"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 6.2 Run below code cell to check whether ADF SHIR is online"
            ],
            "metadata": {
                "azdata_cell_guid": "dbf8495c-9e6d-430c-8b8a-8e0af7c37701"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Check the status of ADF SHIR\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCtlGET = Invoke-RestMethod -Uri $ControllersURL -Header $headers -Method GET -ContentType \"application/json\" -UseBasicParsing\r\n",
                "$ResponseOfCtlGET | ConvertTo-Json\r\n",
                "if ( $ResponseOfCtlGET.properties.integrationRuntimeState -eq \"Online\" ){\r\n",
                "    Write-Output \"Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR) is ONLINE. Run code in step 7 to start migration\"\r\n",
                "} else {\r\n",
                "    Write-Output \"Azure Data Factory (ADF) Self Hosted Integration Runtime (SHIR) is NOT ONLINE.\"\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "2737ddad-ef55-40f9-b342-fa6f7385f2b5"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 7\\. Start MI migration\n",
                "\n",
                "Run below code cell to start the migration."
            ],
            "metadata": {
                "azdata_cell_guid": "5c529b9d-0e59-4570-b498-1ddb1784db7c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "#Start MI migration\r\n",
                "$MIRequestBody = '\r\n",
                "{\r\n",
                "      \"location\": null,\r\n",
                "      \"properties\": {\r\n",
                "        \"SourceDatabaseName\": null,\r\n",
                "        \"MigrationController\": null,\r\n",
                "        \"AutoCutoverConfiguration\": null,\r\n",
                "        \"BackupConfiguration\": {\r\n",
                "          \"TargetLocation\": {\r\n",
                "            \"StorageAccountResourceId\": null,\r\n",
                "            \"AccountKey\": null\r\n",
                "          },\r\n",
                "          \"SourceLocation\": {\r\n",
                "            \"FileShare\": {\r\n",
                "              \"Path\": null,\r\n",
                "              \"Username\": null,\r\n",
                "              \"Password\": null\r\n",
                "            }\r\n",
                "          }\r\n",
                "        },\r\n",
                "        \"SourceSqlConnection\": {\r\n",
                "          \"DataSource\": null,\r\n",
                "          \"UserName\": null,\r\n",
                "          \"Password\": null,\r\n",
                "          \"Authentication\": null\r\n",
                "        },\r\n",
                "        \"Scope\": null\r\n",
                "      }\r\n",
                "}' | ConvertFrom-Json\r\n",
                "\r\n",
                "$AutoCutoverConfiguration = '\r\n",
                "{\r\n",
                " \"LastBackupName\": null\r\n",
                "}' | ConvertFrom-Json\r\n",
                "\r\n",
                "$MIRequestBody.location = $Location\r\n",
                "$MIRequestBody.properties.SourceDatabaseName = $SourceDbName\r\n",
                "$MIRequestBody.properties.MigrationController = $ControllersRescourceId \r\n",
                "$MIRequestBody.properties.BackupConfiguration.TargetLocation.StorageAccountResourceId = $StorageAccountResourceId\r\n",
                "$MIRequestBody.properties.BackupConfiguration.TargetLocation.AccountKey = $StorageAccountKey\r\n",
                "$MIRequestBody.properties.BackupConfiguration.SourceLocation.FileShare.Path = $FileSharePath\r\n",
                "$MIRequestBody.properties.BackupConfiguration.SourceLocation.FileShare.Username = $FileShareUserName\r\n",
                "$MIRequestBody.properties.BackupConfiguration.SourceLocation.FileShare.Password = $FileSharePassword\r\n",
                "$MIRequestBody.properties.SourceSqlConnection.DataSource = $SourceSQLServer\r\n",
                "$MIRequestBody.properties.SourceSqlConnection.UserName = $sourceSQLServerUserName\r\n",
                "$MIRequestBody.properties.SourceSqlConnection.Password = $sourceSQLServerPassword\r\n",
                "$MIRequestBody.properties.SourceSqlConnection.Authentication = $Authentication\r\n",
                "$MIRequestBody.properties.Scope = \"/subscriptions/\" + $subscription + \"/resourceGroups/\" + $MIRgName + \"/providers/Microsoft.Sql/managedInstances/\" + $MIName\r\n",
                "\r\n",
                "if($MigrationMode -eq \"offline\"){\r\n",
                "    $AutoCutoverConfiguration.LastBackupName = $LastBackupName\r\n",
                "    $properties.AutoCutoverConfiguration= $AutoCutoverConfiguration\r\n",
                "}\r\n",
                "\r\n",
                "$MIRequestBodyJson = $MIRequestBody | ConvertTo-Json -Depth 5\r\n",
                "\r\n",
                "$MIMigrationRescourceId = \"/subscriptions/\" + $subscription + \"/resourceGroups/\" + $MIRgName + \"/providers/Microsoft.Sql/managedInstances/\" + $MIName + \"/providers/Microsoft.DataMigration/databaseMigrations/\" + $TargetDbName\r\n",
                "\r\n",
                "$MIMigrationURL = $HostName +  $MIMigrationRescourceId + \"?\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCMIMigrationPUT = Invoke-WebRequest -Uri $MIMigrationURL -Header $headers -Method PUT -Body $MIRequestBodyJson -ContentType \"application/json\" -UseBasicParsing\r\n",
                " \r\n",
                "Write-Output \"View the response of PUT request\"\r\n",
                "$ResponseOfCMIMigrationPUT"
            ],
            "metadata": {
                "azdata_cell_guid": "cd01878c-850b-4cee-8489-2458dd159da0",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 8\\. Monitor ongoing migration\n",
                "\n",
                "Run below code sell to check the detailed status of database migration . Time taken for migration will depend on number of files and their size.  \n",
                "Periodically run this cell until all backup files are restored."
            ],
            "metadata": {
                "azdata_cell_guid": "3ff15d86-4538-4933-897b-cd7f76191db4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Monitor ongoing migration\r\n",
                "$MIMigrationGETURL = $HostName +  $MIMigrationRescourceId + \"?\" + \"$\" + \"expand=MigrationStatusDetails&\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCMIMigrationGET = Invoke-RestMethod -Uri $MIMigrationGETURL -Header $headers -Method GET -ContentType \"application/json\" -UseBasicParsing\r\n",
                "\r\n",
                "Write-Output \"View the whole response: \"\r\n",
                "$ResponseOfCMIMigrationGET | ConvertTo-Json\r\n",
                "Write-Output \"Backup file status details: \"\r\n",
                "$html = $ResponseOfCMIMigrationGET.properties.migrationStatusDetails.activeBackupSets\r\n",
                "$htmlSelect = $html | Select-Object -Property @{Name=\"fileName\"; E={$_.listOfBackupFiles.fileName}}, @{Name=\"fileStatus\"; E={$_.listOfBackupFiles.status}}, backupType, isBackupRestored\r\n",
                "$htmlSelect | Format-Table \r\n",
                "$operationid = $ResponseOfCMIMigrationGET.properties.migrationOperationId\r\n",
                "if( $ResponseOfCMIMigrationGET.properties.migrationStatusDetails.isFullBackupRestored ){\r\n",
                "    Write-Output \"All the backup files have been restored. Run code in step 9.1 to initiate migration cutover.\"\r\n",
                "}else {\r\n",
                "    Write-Output \"Backup files are being restored...\"\r\n",
                "}\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b77d3768-d8aa-47bb-8049-7f636f17641c",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 9\\. Initiate migration cutover"
            ],
            "metadata": {
                "azdata_cell_guid": "1c628e15-c2ee-4089-8f33-4d8188d916b5"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 9.1 Run below code cell to intiate migration cutover."
            ],
            "metadata": {
                "azdata_cell_guid": "8b1d5b26-ee3e-4770-aaf4-e4d193824daa"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Initiate migration cutover\r\n",
                "$MIMigrationCutoverURL = $HostName +  $MIMigrationRescourceId +\"/operations/\" + $operationid + \"/cutover?\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCMICutover = Invoke-WebRequest -Uri $MIMigrationCutoverURL -Header $headers -Method POST -ContentType \"application/json\" -UseBasicParsing\r\n",
                "# View the reponse of cutover request\r\n",
                "$ResponseOfCMICutover\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f6ae080f-522d-40cb-9037-2aaca7357585",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### 9.2 Run below code cell to ensure cutover has succeeded."
            ],
            "metadata": {
                "azdata_cell_guid": "a982268b-bc10-4aea-a805-224444b6223a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Confirm cutover has succeeded\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCMIMigrationGET = Invoke-RestMethod -Uri $MIMigrationGETURL -Header $headers -Method GET -ContentType \"application/json\"  -UseBasicParsing\r\n",
                "Write-Output \"Status of migration cutover\"\r\n",
                "$ResponseOfCMIMigrationGET | ConvertTo-Json\r\n",
                "if($ResponseOfCMIMigrationGET.properties.migrationStatus -eq \"Succeeded\"){\r\n",
                "    Write-Output \"Migration has succeeded.\"\r\n",
                "}else{\r\n",
                "    Write-Output \"Migration cutover is in progress. Check the status again in a moment... \"\r\n",
                "}\r\n",
                "# loop in the above code cell after a few mins? \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a79bfa1e-3c69-45c0-8990-608291d31c5f"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## 10\\. Check status of migrated database\n",
                "\n",
                "Run below code cell to check status of the migrated database."
            ],
            "metadata": {
                "azdata_cell_guid": "349066ee-7d9a-45c4-9b64-72c1fbbf2450"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Check the status of target database after migration\r\n",
                "Write-Output \"Check the status of target database after migration\"\r\n",
                "Get-AzSqlInstanceDatabase -Name $TargetDbName -InstanceName $MIName -ResourceGroupName $MIRgName"
            ],
            "metadata": {
                "azdata_cell_guid": "303ae239-059a-41b2-8a8d-27e3c77f36a7"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Appendix"
            ],
            "metadata": {
                "azdata_cell_guid": "80a2e85c-418a-4171-81ea-ced7ed3312b9"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### A) Cancel migration\n",
                "\n",
                "Run below code cell if there is a need to cancel an ongoing migration."
            ],
            "metadata": {
                "azdata_cell_guid": "b75f8c68-0926-4b56-805a-f3ce9f5eac35"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$MIMigrationCancelURL = $HostName +  $MIMigrationRescourceId +\"/operations/\" + $operationid + \"/cancel?\" + $APIversion\r\n",
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCMICancel = Invoke-WebRequest -Uri $MIMigrationCancelURL -Header $headers -Method POST -ContentType \"application/json\" -UseBasicParsing\r\n",
                "# View the reponse of Cancel request\r\n",
                "$ResponseOfCMICancel"
            ],
            "metadata": {
                "azdata_cell_guid": "954cb49f-2a3d-4611-941c-5963e3d6533d",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### B) Delete migration controllers\n",
                "\n",
                "Run below code cell if there is a need to delete an migration controller"
            ],
            "metadata": {
                "azdata_cell_guid": "88773698-2ef2-43b0-ab29-25d359850fc2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$tokentext = (Get-AzAccessToken).Token\r\n",
                "$headers = @{Authorization=\"Bearer $tokentext\"}\r\n",
                "$ResponseOfCtlDELETE = Invoke-WebRequest -Uri $ControllersURL -Header $headers -Method Delete -ContentType \"application/json\" -UseBasicParsing\r\n",
                "$ResponseOfCtlDELETE"
            ],
            "metadata": {
                "azdata_cell_guid": "d2e5ae3c-d4cc-4f17-b5b5-09195e6ac0ad"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}